﻿/*
************************************************************
CreateDate  *  Author        *  Content
2008-07-04  *  Donne.Yang  *  Query data and execute data
************************************************************
NO   *  UpdateDate   *  Author        *  Content

*************************************************************
*/
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Collections;
using System.Configuration;

namespace Smg.DAL
{
    public class MsSqlDBOpration
    {
        private string strConn;//DB

        public MsSqlDBOpration()
        {
            if (Configuration.MsSqlConn == null)
            {
                this.strConn = ConfigurationSettings.AppSettings["MsSqlConn"];
                Configuration.MsSqlConn = ConfigurationSettings.AppSettings["MsSqlConn"];
            }
            else
            {
                this.strConn = Configuration.MsSqlConn;
            }
        }

        public MsSqlDBOpration(string strConn)
        {
            this.strConn = strConn;
        }

        /// <summary>
        /// Connect Sql DB 
        /// </summary>
        /// <returns>Sql DB Connection</returns>
        private SqlConnection GetConnection()
        {
            SqlConnection sqlConnection = null;

            try
            {
                sqlConnection = new SqlConnection(strConn);
                sqlConnection.Open();
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return sqlConnection;
        }

        /// <summary>
        /// Close Sql DB Connection
        /// </summary>
        /// <param name="sqlConnection">SqlConnection</param>
        private void CloseDB(SqlConnection sqlConnection)
        {
            if (sqlConnection.State != ConnectionState.Closed)
            {
                try
                {
                    sqlConnection.Close();
                    sqlConnection.Dispose();
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
        }

        public string TestConnection()
        {
            SqlConnection sqlConnection = null;
            try
            {
                sqlConnection = new SqlConnection(strConn);
                sqlConnection.Open();
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return "OK";
        }

        /// <summary>
        /// Query data from sql DB
        /// </summary>
        /// <param name="strSql">sql sentence</param>
        /// <returns>data with dataset format</returns>
        public DataSet GetResult(string strSql)
        {
            SqlConnection sqlConnection = GetConnection();

            DataSet dsData = new DataSet();

            try
            {
                SqlDataAdapter sqlDataAdapter = new SqlDataAdapter();

                sqlDataAdapter.SelectCommand = new SqlCommand(strSql, sqlConnection);

                sqlDataAdapter.Fill(dsData);
                sqlDataAdapter.Dispose();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                CloseDB(sqlConnection);
            }

            return dsData;
        }

        /// <summary>
        /// Query data from sql DB
        /// </summary>
        /// <param name="strSql">sql sentence</param>
        /// <returns>data with datareader format</returns>
        public SqlDataReader GetResultRead(string strSql)
        {
            SqlConnection sqlConnection = GetConnection();

            SqlDataReader sqlDataReader = null;

            try
            {
                SqlCommand sqlCommand = new SqlCommand(strSql, sqlConnection);

                sqlDataReader = sqlCommand.ExecuteReader();
                sqlCommand.Dispose();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                CloseDB(sqlConnection);
            }

            return sqlDataReader;
        }

        /// <summary>
        /// update SQL server data of sql DB
        /// </summary>
        /// <param name="strSql">sql sentence</param>
        /// <returns>execute state with ture or false</returns>
        public bool UpdateData(string strSql)
        {
            bool blnFlag = false;

            SqlConnection sqlConnection = GetConnection();

            try
            {
                SqlCommand sqlCommand = new SqlCommand(strSql, sqlConnection);

                if (sqlCommand.ExecuteNonQuery() > 0)
                {
                    blnFlag = true;
                }
                sqlCommand.Dispose();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                CloseDB(sqlConnection);
            }

            return blnFlag;
        }

        /// <summary>
        /// update SQL server data with parameters
        /// </summary>
        /// <param name="strSql">sql string</param>
        /// <param name="Parameters">parameters</param>
        /// <returns>bool</returns>
        public bool UpdateData(string strSql, SqlParameter[] Parameters)
        {
            bool blnFlag = false;
            int num = 0;

            SqlConnection sqlConnection = GetConnection();

            try
            {
                SqlCommand sqlCommand = sqlConnection.CreateCommand();
                sqlCommand.CommandText = strSql;
                sqlCommand.CommandType = CommandType.Text;
                sqlCommand.Parameters.AddRange(Parameters);

                num = sqlCommand.ExecuteNonQuery();
                sqlCommand.Parameters.Clear();
                sqlCommand.Dispose();

                if (num > 0)
                {
                    blnFlag = true;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                CloseDB(sqlConnection);
            }

            return blnFlag;
        }

        /// <summary>
        /// Execute multiple sql sentences of update orcale DB
        /// </summary>
        /// <param name="arrSqls">sql sentences</param>
        /// <returns>execute state with ture or false</returns>
        public bool ExecuteTransaction(ArrayList arrSqls)
        {
            bool blnFlag = false;

            SqlConnection sqlConnection = GetConnection();

            SqlTransaction sqlTransaction = sqlConnection.BeginTransaction();
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.Transaction = sqlTransaction;

            try
            {
                //SqlString rowId;

                for (int i = 0; i < arrSqls.Count; i++)
                {
                    sqlCommand.CommandText = (string)arrSqls[i];
                    sqlCommand.ExecuteNonQuery();
                }

                sqlTransaction.Commit();

                blnFlag = true;
            }
            catch (Exception ex)
            {
                try
                {
                    sqlTransaction.Rollback();
                }
                catch (Exception ex2)
                {
                    if (sqlTransaction.Connection != null)
                    {
                        throw ex2;
                    }
                }
                throw ex;
            }
            finally
            {
                sqlCommand.Dispose();
                CloseDB(sqlConnection);
            }

            return blnFlag;
        }
    }
}
